﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace PingYou.ADO
{
    class Program
    {
        static void ConnectDB()
        {
            //string connString  = "Server=.;DataBase=CourseManageDB;Uid=sa;Pwd=zh!@#123";
            //创建数据库连接对象
            string connString = "Data Source=.;Initial Catalog=Student;Integrated Security=True";
            SqlConnection conn = new SqlConnection(connString);

            //打开连接
            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                Console.WriteLine("数据库打开成功");
            }
            conn.Close();

            if (conn.State == ConnectionState.Closed)
            {
                Console.WriteLine("数据库关闭成功");
            }
        }

        //ExacutedInsert ExacutedUpdate ExacutedDelete
        static void ExacutedInsert()
        {
            string connString = "Data Source=.;Initial Catalog=Student;Integrated Security=True";
            SqlConnection conn = new SqlConnection(connString);

            //定义sql语句

            string sql = "";

            //【2】创建Command对象
            SqlCommand cmd = new SqlCommand(sql, conn);

            //【3】打开数据库连接
            conn.Open();

            //怕【4】执行操作（下面这个方法，只能用于执行insert、update、delete）
            int result = cmd.ExecuteNonQuery();
            Console.WriteLine("受影响的行数=" + result);

            //【5】关闭连接
            conn.Close();
        }

        //static void ExacutedSelect()
        //{
        //    //创建数据库连接对象
        //    string connString = "Data Source=.;Initial Catalog=Student;Integrated Security=True";
        //    SqlConnection conn = new SqlConnection(connString);

        //    SqlDataReader read = new SqlDataReader();
        //    SqlDataAdapter adp = new SqlDataAdapter();
        //    adp.Fill(,)
        //}

        static void ExacuteSingleResult()
        {
            //【1】创建连接对象
            string connString = "Data Source=.;Initial Catalog=Student;Integrated Security=True";
            SqlConnection conn = new SqlConnection(connString);

            //定义sql语句
            string sql = "select ";
            sql = "insert";

            //【2】创建Command对象
            SqlCommand cmd = new SqlCommand(sql, conn);

            //【3】打开数据库连接
            conn.Open();

            //【4】执行查询(下面这个方法，一般执行的都是查询，但是有时候也可以执行同时执行insert操作-将插入的数据ID返回)
            object result = cmd.ExecuteScalar();
            //int result = cmd.ExecuteNonQuery();
            Console.WriteLine("查询结果=" + result);

            //【5】关闭连接
            conn.Close();
        }

        static void ExacuteResult()
        {
            //【1】创建连接对象
            string connString = "Data Source=.;Initial Catalog=Student;Integrated Security=True";
            SqlConnection conn = new SqlConnection(connString);

            //定义sql语句
            string sql = "select ";

            //【2】创建Command对象
            SqlCommand cmd = new SqlCommand(sql, conn);

            //【3】打开数据库连接
            conn.Open();

            //【4】执行查询
            SqlDataReader reader = cmd.ExecuteReader();//数据读取器

            //判断是否有查询结果，来决定读取数据
            while (reader.Read())
            {
                Console.WriteLine($"{reader["CourseName"]}\t{reader["CourseName"]}");
            }
            reader.Close(); //关闭读取器对象( 千万不要忘记关闭)资源占用，以后打不开
            //int result = cmd.ExecuteNonQuery();
            //Console.WriteLine("查询结果=" + result);

            //【5】关闭连接
            conn.Close();
        }

    }
}
